package PhotoController;


import java.sql.*;

import java.util.ArrayList;
import java.util.HashMap;

import Connect.connector;

public class PhotoFreeDAO{
	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	public PhotoFreeBean pt_free_recom_top(){
		conn = connector.getConnection();
		PhotoFreeBean photoFreeBean=new PhotoFreeBean();
		try{
	
			String sql;	
			
			sql = "select z.*,a.* from PHOTOZON z,PHOTOADD a where z.Id=a.Photozon_Id and z.po_recom in (select max(po_recom) from PHOTOZON)";
			pstmt = conn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			if(rs.next()){
				photoFreeBean.setID(rs.getInt("id"));
				photoFreeBean.setPhoto(rs.getString("photo"));
				photoFreeBean.setPo_Title(rs.getString("po_title"));
			}
		}catch(Exception e){
			e.printStackTrace();
		
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return photoFreeBean;
	}
	public ArrayList<PhotoFreeBean> pt_free_recom(){
		conn = connector.getConnection();
		ArrayList<PhotoFreeBean> datas=new ArrayList<PhotoFreeBean>();
		try{
	
			String sql;	
			
			sql = "select * from (select z.*,p.* from PHOTOZON z,PHOTOADD p where z.ID=p.photozon_id order by po_recom desc) a where a.po_recom limit 1,4";

			pstmt = conn.prepareStatement(sql);
			
			rs=pstmt.executeQuery();
			while(rs.next()){
				PhotoFreeBean photoFreeBean1=new PhotoFreeBean();
				photoFreeBean1.setID(rs.getInt("id"));
				photoFreeBean1.setPhoto(rs.getString("photo"));
				photoFreeBean1.setPo_Title(rs.getString("po_title"));
		
				datas.add(photoFreeBean1);
			}
			
			
		}catch(Exception e){
			e.printStackTrace();
		
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return datas;
	}
	
	public ArrayList<PhotoFreeBean> pt_free_bestview(){
		conn = connector.getConnection();
		ArrayList<PhotoFreeBean> datas=new ArrayList<PhotoFreeBean>();
		try{
			
			String sql;	
			
			sql = "select * from (select z.*,p.* from PHOTOZON z,PHOTOADD p where z.ID=p.photozon_id order by po_viewcount desc) a where a.po_viewcount limit 0,8";

			pstmt = conn.prepareStatement(sql);
			
			rs=pstmt.executeQuery();
			while(rs.next()){
				PhotoFreeBean photoFreeBean1=new PhotoFreeBean();
				photoFreeBean1.setID(rs.getInt("id"));
				photoFreeBean1.setPo_Nick(rs.getString("po_nick"));
				photoFreeBean1.setPhoto(rs.getString("photo"));
				photoFreeBean1.setPo_Title(rs.getString("po_title"));
				photoFreeBean1.setPo_Viewcount(rs.getInt("po_viewcount"));
				datas.add(photoFreeBean1);
			}
		}catch(Exception e){
			e.printStackTrace();
		
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return datas;
	}
	
	public ArrayList<PhotoFreeBean> pt_free_list(HashMap param){ 
		conn = connector.getConnection();
		ArrayList<PhotoFreeBean> datas2=new ArrayList<PhotoFreeBean>();
		try{
		
			if(param.get("searchword")==null || "".equals(param.get("searchword"))){
				String sql;	
				sql="select z.*,a.* from PHOTOZON z, PHOTOADD a where z.id=a.photozon_id order by z.id desc limit ?,? ";
				pstmt=conn.prepareStatement(sql);
				pstmt.setInt(1, (Integer)param.get("startNum"));
				pstmt.setInt(2, (Integer)param.get("pageSize"));
				rs=pstmt.executeQuery();
			}else {
			String sql;	
			
			sql = "select z.*,a.* from PHOTOZON z, PHOTOADD a where z.id=a.photozon_id and "+param.get("search") +" like ? order by z.id desc limit ?,? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "%"+(String)param.get("searchword")+"%");
			pstmt.setInt(2, (Integer)param.get("startNum"));
			pstmt.setInt(3, (Integer)param.get("pageSize"));
			rs=pstmt.executeQuery();
			}
			while(rs.next()){
				PhotoFreeBean photoFreeBean1=new PhotoFreeBean();
				photoFreeBean1.setID(rs.getInt("id"));
				photoFreeBean1.setMembers_Id(rs.getInt("members_id"));
				photoFreeBean1.setPo_Contents(rs.getString("po_contents"));
				photoFreeBean1.setPo_Date(rs.getDate("po_date"));
				photoFreeBean1.setPo_Nick(rs.getString("po_nick"));
				photoFreeBean1.setPo_recom(rs.getInt("po_recom"));
				photoFreeBean1.setPo_Viewcount(rs.getInt("po_viewcount"));
				photoFreeBean1.setPhoto(rs.getString("photo"));
				photoFreeBean1.setPo_Title(rs.getString("po_title"));
				photoFreeBean1.setPo_Comcount(rs.getInt("po_comcount"));
		
				datas2.add(photoFreeBean1);
			}
		}catch(Exception e){
			e.printStackTrace();
		
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return datas2;
	}
	
	public ArrayList<PhotoFreeBean> pt_free_list2(HashMap param){ 
		conn = connector.getConnection();
		ArrayList<PhotoFreeBean> datas2=new ArrayList<PhotoFreeBean>();
		try{
				String sql;	
				sql="select z.*,a.* from PHOTOZON z, PHOTOADD a where z.id=a.photozon_id order by z.id desc limit 0,8 ";
				pstmt=conn.prepareStatement(sql);
				//pstmt.setInt(1, (Integer)param.get("startNum"));
				//pstmt.setInt(2, (Integer)param.get("pageSize"));
				rs=pstmt.executeQuery();
			
			while(rs.next()){
				PhotoFreeBean photoFreeBean1=new PhotoFreeBean();
				photoFreeBean1.setID(rs.getInt("id"));
				photoFreeBean1.setMembers_Id(rs.getInt("members_id"));
				photoFreeBean1.setPo_Contents(rs.getString("po_contents"));
				photoFreeBean1.setPo_Date(rs.getDate("po_date"));
				photoFreeBean1.setPo_Nick(rs.getString("po_nick"));
				photoFreeBean1.setPo_recom(rs.getInt("po_recom"));
				photoFreeBean1.setPo_Viewcount(rs.getInt("po_viewcount"));
				photoFreeBean1.setPhoto(rs.getString("photo"));
				photoFreeBean1.setPo_Title(rs.getString("po_title"));
				photoFreeBean1.setPo_Comcount(rs.getInt("po_comcount"));
		
				datas2.add(photoFreeBean1);
			}
		}catch(Exception e){
			e.printStackTrace();
		
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return datas2;
	}
	public Integer pfTotalCount(HashMap param){
		conn = connector.getConnection();
		int count=0;
		String sql;	
		try{
		
			if(param.get("searchword")==null || "".equals(param.get("searchword"))){
				sql="select count(*) as cnt from PHOTOZON z, PHOTOADD a where z.id=a.photozon_id";
				pstmt=conn.prepareStatement(sql);
				rs=pstmt.executeQuery();
				if(rs.next()){
			    	count=rs.getInt("cnt");
			    }
				
			}else {
			
			
			sql="select count(*) as cnt from PHOTOZON z, PHOTOADD a where z.id=a.photozon_id and "+param.get("search") +" like ? ;";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "%"+(String)param.get("searchword")+"%");
			rs=pstmt.executeQuery();
			}
			if(rs.next()){
		    	count=rs.getInt("cnt");
		    	System.out.println(count);
		    }
		}catch(Exception e){
			e.printStackTrace();
		
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return count;
		
	}
	
	public void pt_free_write(PhotoFreeBean photoFreeBean){
		conn = connector.getConnection();
		PhotoFreeBean photoFreeBean1=new PhotoFreeBean();
		try{
		
			String sql1;
			String sql2;
			String sql3;
			String sql4;
			sql4="select id from MEMBERS where mem_nick=?";
			pstmt = conn.prepareStatement(sql4);
			pstmt.setString(1, photoFreeBean.getPo_Nick());
			rs=pstmt.executeQuery();
			int id=0;
			if(rs.next()){
				id=rs.getInt("id");
			}
			sql1="insert into PHOTOZON(po_recom,ID,members_id,po_title,po_nick,po_date,po_viewcount,po_contents,po_comcount) values (0,null,?,?,?,Sysdate(),0,?,0)";
			sql2="select max(id) from PHOTOZON";
			sql3="insert into PHOTOADD(photo,photozon_id) values (?,?)";
			
			pstmt = conn.prepareStatement(sql1);
			pstmt.setInt(1, id);
			pstmt.setString(2, photoFreeBean.getPo_Title());
			pstmt.setString(3, photoFreeBean.getPo_Nick());
			pstmt.setString(4, photoFreeBean.getPo_Contents());
			pstmt.executeUpdate();
			
			pstmt=conn.prepareStatement(sql2);
			rs=pstmt.executeQuery();
			if(rs.next()){
				photoFreeBean1.setID(rs.getInt("max(id)"));
			}
			
			pstmt = conn.prepareStatement(sql3);
			pstmt.setString(1, photoFreeBean.getPhoto());
			pstmt.setInt(2, photoFreeBean1.getID());
			pstmt.executeUpdate();
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
	}
	
	public PhotoFreeBean pt_free_read(int id){
		conn = connector.getConnection();
		PhotoFreeBean photoFreeBean=new PhotoFreeBean();
		
		try{
	
			String sql;		
			String sql2;
			String sql3;
			sql = "select z.*,a.* from PHOTOZON z, PHOTOADD a where z.id=a.photozon_id and z.id=?";
			sql2="select c.*,z.* from COMMENTPHOTO c ,PHOTOZON z where c.Photozon_Id=z.ID and z.ID=?;";
			sql3="update PHOTOZON set po_viewcount=po_viewcount+1 where id=?";
			
			 pstmt=conn.prepareStatement(sql3);
			 pstmt.setInt(1, id);
			 pstmt.executeUpdate();
			
			 pstmt=conn.prepareStatement(sql);
			 pstmt.setInt(1, id);
			 rs= pstmt.executeQuery();
			 if(rs.next()){

					photoFreeBean.setID(rs.getInt("id"));
					photoFreeBean.setMembers_Id(rs.getInt("members_id"));
					photoFreeBean.setPo_Contents(rs.getString("po_contents"));
					photoFreeBean.setPo_Date(rs.getDate("po_date"));
					photoFreeBean.setPo_Nick(rs.getString("po_nick"));
					photoFreeBean.setPo_recom(rs.getInt("po_recom"));
					photoFreeBean.setPo_Viewcount(rs.getInt("po_viewcount"));
					photoFreeBean.setPhoto(rs.getString("photo"));
					photoFreeBean.setPo_Title(rs.getString("po_title"));
			 }
			 
			 pstmt=conn.prepareStatement(sql2);
			 pstmt.setInt(1, id);
			 rs= pstmt.executeQuery();
			 
			 while(rs.next()){
				 PhotoCommentBean photoCommentBean=new PhotoCommentBean();
			 }
			 
			 
		}catch(Exception e){
			e.printStackTrace();
		
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return photoFreeBean;
	}
	
	public ArrayList<PhotoCommentBean> pt_free_commentr(int id){
		conn = connector.getConnection();
		ArrayList<PhotoCommentBean> datas=new ArrayList<PhotoCommentBean>();
		try{
			
			String sql;		
			
			sql="select c.*,z.* from COMMENTPHOTO c ,PHOTOZON z where c.Photozon_Id=z.ID and z.ID=?;";

			 pstmt=conn.prepareStatement(sql);
			 pstmt.setInt(1, id);
			 rs= pstmt.executeQuery();
			 
			 while(rs.next()){
				 PhotoCommentBean photoCommentBean=new PhotoCommentBean();
				 photoCommentBean.setId(rs.getInt("id"));
				 photoCommentBean.setPhotocom_contents(rs.getString("photocom_contents"));
				 photoCommentBean.setPhotocom_date(rs.getDate("photocom_date"));
				 photoCommentBean.setPhotocom_nick(rs.getString("photocom_nick"));
				 photoCommentBean.setPhotozon_id(rs.getInt("photozon_id"));
				 datas.add(photoCommentBean);
			 }
		}catch(Exception e){
			e.printStackTrace();
		
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return datas;
	}
	public void pt_free_modify(PhotoFreeBean photoFreeBean){
		conn = connector.getConnection();
		try{
			
			String sql1;		
			String sql2;
			sql1 = "update PHOTOZON set po_title=?,po_contents=? where id=?";
			sql2 = "update PHOTOADD set photo=? where photozon_id=?";
			 pstmt=conn.prepareStatement(sql1);
			 pstmt.setString(1, photoFreeBean.getPo_Title());
			 pstmt.setString(2, photoFreeBean.getPo_Contents());
			 pstmt.setInt(3, photoFreeBean.getID());
			 pstmt.executeUpdate();
			 
			 pstmt=conn.prepareStatement(sql2);
			 pstmt.setString(1, photoFreeBean.getPhoto());
			 pstmt.setInt(2, photoFreeBean.getID());
			 pstmt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		
	}
	public void pt_free_delete(int id){
		conn = connector.getConnection();
		try{
			
			String sql;
			sql="delete from PHOTOZON where id=?";
			 pstmt=conn.prepareStatement(sql);
			 pstmt.setInt(1, id);
			 pstmt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
	}

	public void pt_free_comment(int id, String photocom_nick,String photocom_contents){
		conn = connector.getConnection();
		try{
			
			String sql;
			String sql2;
			String sql3;
			sql="insert into COMMENTPHOTO (id,photozon_id,photocom_nick,photocom_contents,photocom_date) values (null,?,?,?,Sysdate())";
			 pstmt=conn.prepareStatement(sql);
			 pstmt.setInt(1, id);
			 pstmt.setString(2, photocom_nick);
			 pstmt.setString(3, photocom_contents);
			 pstmt.executeUpdate();
			 
			 sql2="select count(*) cnt from COMMENTPHOTO where photozon_id=?";
			 pstmt=conn.prepareStatement(sql2);
			 pstmt.setInt(1, id);
			 rs=pstmt.executeQuery();
			 int count=0;
			 if(rs.next()){
				 count=rs.getInt("cnt");
				 System.out.println("count==="+count);
			 }
			 
			 sql3="update PHOTOZON set po_comcount=? where id=?";
			 pstmt=conn.prepareStatement(sql3);
			 pstmt.setInt(1, count);
			 pstmt.setInt(2, id);
			 pstmt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
	}
	
	public void pt_free_recom(int id){
		conn = connector.getConnection();
		try{
			
			String sql;
			
			sql="update PHOTOZON set po_recom=po_recom+1 where id=?";
			 pstmt=conn.prepareStatement(sql);
			 pstmt.setInt(1,id);
			 pstmt.executeUpdate();
			 
			 
		}catch(Exception e){
			e.printStackTrace();
		
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
	}
	
	public void pt_free_com_cancle(int com_id){
		conn = connector.getConnection();
		try{
			
			String sql;
			sql="delete from COMMENTPHOTO where id=?";
			 pstmt=conn.prepareStatement(sql);
			 pstmt.setInt(1, com_id);
			 pstmt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
	}
	
	public Integer young_level(String mem_nick){
		conn = connector.getConnection();
		int level=0;
		try{
			
			String sql;	
			sql = "select mem_level from MEMBERS where mem_nick=?";

			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, mem_nick);
			rs=pstmt.executeQuery();
			if(rs.next()){
				level=rs.getInt("mem_level");
			}
			
		}catch(Exception e){
			e.printStackTrace();
		
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return level;
	}
}
